﻿package net.wangit.jdbc.database;

import net.wangit.jdbc.JDBCException;
import net.wangit.jdbc.Type;
import net.wangit.util.Checker;

public class Oracle extends Database {

	private static final String DATE_FORMAT = "YYYY-MM-DD";
	private static final String TIME_FORMAT = "HH24:MI:SS";
	private static final String TIMESTAMP_FORMAT = "YYYY-MM-DD HH24:MI:SS";

	public Oracle() {
		registerType(1, 1, "CHAR($p)");
		registerType(2, 12, "VARCHAR2($p)");
		registerType(11, 5, "NUMBER(5,0)");
		registerType(12, 4, "NUMBER(10,0)");
		registerType(13, -5, "NUMBER(19,0)");
		registerType(21, 6, "FLOAT");
		registerType(22, 8, "DOUBLE PRECISION");
		registerType(23, 2, "NUMBER($p, $s)");
		registerType(31, 91, "DATE");
		registerType(32, 92, "DATE");
		registerType(33, 93, "DATE");
		registerType(41, 2005, "CLOB");
		registerType(43, 2004, "BLOB");
	}

	public int getTypeCode(int sqlTypeCode, String columnTypeName,
			int precision, int scale) throws JDBCException {
		switch (sqlTypeCode) {
		case 1:
			return 1;

		case 12:
			return 2;

		case 2:
			if (scale > 0)
				return 23;
			if (precision <= 5)
				return 11;
			return precision > 10 ? 13 : 12;

		case 6:
			return 21;

		case 8:
			return 22;

		case 91:
		case 93:
			return 31;

		case 2005:
			return 41;

		case 2004:
			return 43;
		}
		throw new JDBCException((new StringBuilder(
				"Unsupported sqlType: sqlTypeCode=")).append(sqlTypeCode)
				.append(",columnTypeName=").append(columnTypeName).toString());
	}

	public int getDefaultBatchSize() {
		return 15;
	}

	public String getAddColumnString() {
		return "add";
	}

	public String getSequenceNextValString(String sequenceName) {
		return (new StringBuilder("select ")).append(sequenceName).append(
				".nextval from dual").toString();
	}

	public String getCreateSequenceString(String sequenceName) {
		return (new StringBuilder("create sequence ")).append(sequenceName)
				.toString();
	}

	public String getDropSequenceString(String sequenceName) {
		return (new StringBuilder("drop sequence ")).append(sequenceName)
				.toString();
	}

	public String getCascadeConstraintsString() {
		return " cascade constraints";
	}

	public boolean supportsForUpdateNowait() {
		return true;
	}

	public boolean supportsSequences() {
		return true;
	}

	public boolean supportsLimit() {
		return true;
	}

	public String getLimitString(String sql, boolean hasOffset) {
		StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);
		if (hasOffset)
			pagingSelect
					.append("select * from ( select row_.*, rownum rownum_ from ( ");
		else
			pagingSelect.append("select * from ( ");
		pagingSelect.append(sql);
		if (hasOffset)
			pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");
		else
			pagingSelect.append(" ) where rownum <= ?");
		return pagingSelect.toString();
	}

	public boolean bindLimitParametersInReverseOrder() {
		return true;
	}

	public boolean useMaxForLimit() {
		return true;
	}

	public boolean supportsForUpdateOf() {
		return false;
	}

	public String getQuerySequencesString() {
		return "select SEQUENCE_NAME from user_sequences";
	}

	public String getQueryTableString(String tableName) {
		return (new StringBuilder(
				"select table_name from user_tables where table_name = '"))
				.append(tableName).append("'").toString();
	}

	public String getQueryOption(Type type, String tableAlias, String name,
			String value, boolean isEqual) {
		if (!Checker.isEmpty(tableAlias))
			name = (new StringBuilder(String.valueOf(tableAlias))).append(
					getSchemaSeparator()).append(name).toString();
		value = value.replaceAll("'", "''");
		switch (type.code()) {
		case 1:
		case 2:
		case 41:
			if (isEqual)
				return (new StringBuilder(String.valueOf(name))).append(" = '")
						.append(value).append("'").toString();
			else
				return (new StringBuilder(String.valueOf(name))).append(
						" like '%").append(value).append("%'").toString();

		case 11:
		case 12:
		case 13:
		case 21:
		case 22:
		case 23:
			return (new StringBuilder(String.valueOf(name))).append(" = ")
					.append(value).toString();

		case 31:
			return (new StringBuilder(String.valueOf(name))).append(
					"=TO_DATE('").append(value).append("','").append(
					Oracle.DATE_FORMAT).append("')").toString();

		case 32:
			return (new StringBuilder(String.valueOf(name))).append(
					"=TO_DATE('").append(value).append("','").append(
					Oracle.TIME_FORMAT).append("')").toString();

		case 33:
			if (value != null && value.length() == 10)
				value = (new StringBuilder(String.valueOf(value))).append(
						" 00:00:00").toString();
			return (new StringBuilder(String.valueOf(name))).append(
					"=TO_DATE('").append(value).append("','").append(
					Oracle.TIMESTAMP_FORMAT).append("')").toString();
		}
		throw new UnsupportedOperationException((new StringBuilder(
				"Unsupported sqlType: sqlTypeCode=")).append(type.code())
				.append(",columnName=").append(name).toString());
	}

	public String getScopeQueryOption(Type type, String tableAlias,
			String name, String start, String end) {
		if (!Checker.isEmpty(tableAlias))
			name = (new StringBuilder(String.valueOf(tableAlias))).append(
					getSchemaSeparator()).append(name).toString();
		StringBuffer result = new StringBuffer();
		if (start != null)
			start = start.replaceAll("'", "''");
		if (end != null)
			end = end.replaceAll("'", "''");
		switch (type.code()) {
		case 1:
		case 2:
		case 41:
			if (!Checker.isEmpty(start))
				result.append(name).append(" >= '").append(start).append("'");
			if (!Checker.isEmpty(end)) {
				if (!Checker.isEmpty(start))
					result.append(" and ");
				result.append(name).append(" <= '").append(end).append("'");
			}
			return result.toString();

		case 11:
		case 12:
		case 13:
		case 21:
		case 22:
		case 23:
			if (!Checker.isEmpty(start))
				result.append(name).append(" >= ").append(start);
			if (!Checker.isEmpty(end)) {
				if (!Checker.isEmpty(start))
					result.append(" and ");
				result.append(name).append(" <= ").append(end);
			}
			return result.toString();

		case 31:
			if (!Checker.isEmpty(start))
				result.append(name).append(
						(new StringBuilder(" >= TO_DATE('")).append(start)
								.append("','").append(Oracle.DATE_FORMAT)
								.append("')").toString());
			if (!Checker.isEmpty(end)) {
				if (!Checker.isEmpty(start))
					result.append(" and ");
				result.append(name).append(
						(new StringBuilder(" <= TO_DATE('")).append(end)
								.append("','").append(Oracle.DATE_FORMAT)
								.append("')").toString());
			}
			return result.toString();

		case 32:
			if (!Checker.isEmpty(start))
				result.append(name).append(
						(new StringBuilder(" >= TO_DATE('")).append(start)
								.append("','").append(Oracle.TIME_FORMAT)
								.append("')").toString());
			if (!Checker.isEmpty(end)) {
				if (!Checker.isEmpty(start))
					result.append(" and ");
				result.append(name).append(
						(new StringBuilder(" <= TO_DATE('")).append(end)
								.append("','").append(Oracle.TIME_FORMAT)
								.append("')").toString());
			}
			return result.toString();

		case 33:
			if (!Checker.isEmpty(start)) {
				if (start.length() == 10)
					start = (new StringBuilder(String.valueOf(start))).append(
							" 00:00:00").toString();
				result.append(name).append(
						(new StringBuilder(" >= TO_DATE('")).append(start)
								.append("','").append(Oracle.TIMESTAMP_FORMAT)
								.append("')").toString());
			}
			if (!Checker.isEmpty(end)) {
				if (!Checker.isEmpty(start))
					result.append(" and ");
				if (end.length() == 10)
					end = (new StringBuilder(String.valueOf(end))).append(
							" 23:59:59").toString();
				result.append(name).append(
						(new StringBuilder(" <= TO_DATE('")).append(end)
								.append("','").append(Oracle.TIMESTAMP_FORMAT)
								.append("')").toString());
			}
			return result.toString();
		}
		throw new UnsupportedOperationException((new StringBuilder(
				"Unsupported sqlType: sqlTypeCode=")).append(type.code())
				.append(",columnName=").append(name).toString());
	}
}
